<?php
header("Content-Type:text/html;charset=utf-8");

/**
 *php操作mysql的工具类
 */
class Db
{
    private $_db = null; //数据库连接句柄
    private $_table = null; //表名
    private $_where = null; //where条件
    private $_order = null; //order排序
    private $_limit = null; //limit限定查询
    private $_group = null; //group分组
    private $_sql = null; //SQL语句
    private $_configs = array(
        'dbms' => 'mysql', //数据库类型
        'host' => '127.0.0.1', //数据库主机名
        'dbName' => 'day13', //使用的数据库
        'user' => 'root', //数据库连接用户名
        'pwd' => 'root', //对应的密码
        'tbname' => 'item', //使用的数据表
    ); //数据库配置

    /**
     * 构造函数，连接数据库
     */
    public function __construct($config = '')
    {
        $db = $this->_configs;
        if (!empty($config)) {
            $config = array_intersect_key($config, $db);
            $db = array_replace($db, $config);
            $this->_configs = $db;
        }
        $this->_table = $this->_configs['tbname'];
        $config = $db['dbms'] . ":host=" . $db['host'] . ";dbname=" . $db['dbName'];
        $link = $this->_db;
        if (!$link) {
            $obj = new PDO($config, $db['user'], $db['pwd']);
            $this->_db = $obj;
        }
    }

    /**
     * 查找一条数据
     */
    public function find($id = '')
    {
        $link = $this->_db;
        if (!empty($id)) {
            $this->where("id=$id");
        }
        if (!$link) {
            return false;
        }

        $sql = "SELECT * FROM {$this->_table} {$this->_where} {$this->_order} {$this->_limit}";
        $this->_sql = $sql;
        $stmt = $link->prepare($sql);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    /**
     *    切换数据表
     */
    public function table($table)
    {
        $this->_table = $table;
        return $this;
    }

    /**
     *    切换数据表
     */
    public function sql()
    {
        return $this->_sql;
    }

    /**
     * 实现查询操作
     *
     * @param      string   $fields  The fields
     *
     * @return     boolean  ( description_of_the_return_value )
     */
    public function select($fields = "*")
    {
        $fieldsStr = '';
        $link = $this->_db;
        if (!$link) {
            return false;
        }

        if (is_array($fields)) {
            $fieldsStr = implode(',', $fields);
        } elseif (is_string($fields) && !empty($fields)) {
            $fieldsStr = $fields;
        }
        $sql = "SELECT {$fields} FROM {$this->_table} {$this->_where} {$this->_order} {$this->_limit}";
        $this->_sql = $sql;
        $stmt = $link->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * 统计数据库总条数
     */
    public function count()
    {
        $link = $this->_db;
        if (!$link) {
            return false;
        }

        $sql = "SELECT * FROM {$this->_table} {$this->_where} {$this->_order} {$this->_limit}";
        $this->_sql = $sql;
        $stmt = $link->prepare($sql);
        $stmt->execute();
        return $stmt->rowCount();
    }

    /**
     * order排序
     *
     * @param      string   $order  The order
     *
     * @return     boolean  ( description_of_the_return_value )
     */
    public function order($order = '')
    {
        $orderStr = '';
        $link = $this->_db;
        if (!$link) {
            return false;
        }

        if (is_string($order) && !empty($order)) {
            $orderStr = "ORDER BY " . $order;
        }
        $this->_order = $orderStr;
        return $this;
    }

    /**
     * where条件
     *
     * @param      string  $where  The where
     *
     * @return     <type>  ( description_of_the_return_value )
     */
    public function where($where = '')
    {
        $whereStr = '';
        $link = $this->_db;
        if (!$link) {
            return $link;
        }

        if (is_array($where)) {
            foreach ($where as $key => $value) {
                if ($value == end($where)) {
                    $whereStr .= "`" . $key . "` = '" . $value . "'";
                } else {
                    $whereStr .= "`" . $key . "` = '" . $value . "' AND ";
                }
            }
            $whereStr = "WHERE " . $whereStr;
        } elseif (is_string($where) && !empty($where)) {
            $whereStr = "WHERE " . $where;
        }
        $this->_where = $whereStr;
        return $this;
    }

    /**
     * group分组
     *
     * @param      string   $group  The group
     *
     * @return     boolean  ( description_of_the_return_value )
     */
    public function group($group = '')
    {
        $groupStr = '';
        $link = $this->_db;
        if (!$link) {
            return false;
        }

        if (is_array($group)) {
            $groupStr = "GROUP BY " . implode(',', $group);
        } elseif (is_string($group) && !empty($group)) {
            $groupStr = "GROUP BY " . $group;
        }
        $this->_group = $groupStr;
        return $this;
    }

    /**
     * limit限定查询
     *
     * @param      string  $limit  The limit
     *
     * @return     <type>  ( description_of_the_return_value )
     */
    public function limit($limit = '')
    {
        $limitStr = '';
        $link = $this->_db;
        if (!$link) {
            return $link;
        }

        if (is_string($limit) || !empty($limit)) {
            $limitStr = "LIMIT " . $limit;
        } elseif (is_numeric($limit)) {
            $limitStr = "LIMIT " . $limit;
        }
        $this->_limit = $limitStr;
        return $this;
    }

    //    page分页
    public function page($p, $n)
    {
        $limitStr = '';
        $link = $this->_db;
        if (!$link) {
            return $link;
        }

        $off = ($p - 1) * $n;
        if (is_numeric($p) && !empty($p) && is_numeric($n) && !empty($n)) {
            $limitStr = "LIMIT " . $off . "," . $n;
        }
        $this->_limit = $limitStr;
        return $this;
    }

    /**
     * 执行sql语句
     *
     * @param      <type>   $sql    The sql
     *
     * @return     boolean  ( description_of_the_return_value )
     */
    public function execute($sql = null)
    {
        $link = $this->_db;
        if (!$link) {
            return false;
        }

        $this->_sql = $sql;
        $res = $link->exec($sql);
        return $res;
    }

    //    query
    public function query($sql = null)
    {
        $link = $this->_db;
        if (!$link) {
            return false;
        }

        $this->_sql = $sql;
        $stmt = $link->prepare($sql);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    /**
     * 插入数据
     *
     * @param      <type>   $data   The data
     *
     * @return     boolean  ( description_of_the_return_value )
     */
    public function insert($data)
    {
        $link = $this->_db;
        if (!$link) {
            return false;
        }

        if (is_array($data)) {
            $keys = '';
            $values = '';
            foreach ($data as $key => $value) {
                $keys .= "`" . $key . "`,";
                $values .= "'" . $value . "',";
            }
            $keys = rtrim($keys, ',');
            $values = rtrim($values, ',');
        }
        $sql = "INSERT INTO `{$this->_table}`({$keys}) VALUES({$values})";
        $this->_sql = $sql;
        $insertId = $link->exec($sql);
        return $insertId;
    }

    /**
     * 更新数据
     *
     * @param      <type>  $data   The data
     *
     * @return     <type>  ( description_of_the_return_value )
     */
    public function update($data)
    {
        $link = $this->_db;
        if (!$link) {
            return $link;
        }

        if (is_array($data)) {
            $dataStr = '';
            foreach ($data as $key => $value) {
                $dataStr .= "`" . $key . "`='" . $value . "',";
            }
            $dataStr = rtrim($dataStr, ',');
        }
        $sql = "UPDATE `{$this->_table}` SET {$dataStr} {$this->_where} {$this->_order} {$this->_limit}";
        $this->_sql = $sql;
        $res = $link->exec($sql);
        return $res;
    }

    /**
     * 删除数据
     *
     * @return     <type>  ( description_of_the_return_value )
     */
    public function delete($id = '')
    {
        $link = $this->_db;
        if (!empty($id)) {
            $this->where("id=$id");
        }
        if (!$link) {
            return $link;
        }

        if (empty($this->_where)) {
            return 0;
        }
        //防止数据库数据被全部删除
        $sql = "DELETE FROM `{$this->_table}` {$this->_where}";
        $this->_sql = $sql;
        $res = $link->exec($sql);
        return $res;
    }

}

if (!function_exists('db')) {
    function db($tbname = '')
    {
        $obj = new Db($_ENV['db']);
        if (!empty($tbname)) {
            $obj->table($tbname);
        }
        return $obj;
    }
}
